MySQL 常用函数学习
分组函数
又称为 聚合函数 或 组函数
-- sum 求和
-- avg 平均值
-- max 最大值
-- min 最小值
-- count(*) 计算个数（可以搭配去重使用）
select count(distinct 字段) from 表
字符函数
-- 获取参数的字节字数（注意不是字符而是字节）
select temp_name, length(temp_name) from tb_temp;
-- 如下：获取的只是字节数（这个编码下中文三个字节）
-- '张三', 6
-- '李四', 6
-- '王五', 6
-- 拼接函数（concat）
select concat(last_name,' ',first_name) as 'name' from 表名;
-- 大写upper 小写lower
select upper(temp_name) from tb_temp;
-- 举个例子 姓名中首字符大写，其他字符小写然后用 _ 拼接
select concat(upper(substring(last_name,1,1)),'_',lower(substring(last_name,2))) from tb_user;
-- 截取子字符串 substr 有些地方写做 substring
-- substring('字符串',开始索引，长度) 不写长度表示到结尾
-- 以字符为单位，且从 1 开始
select substr(temp_name, 1 ,1) from tb_temp;
-- instr：返回字串出现的索引，找不到则返回 0
-- 例：instr('helloworld','wo') 返回 6
select instr(temp_name, 'als') from tb_temp;
-- trim 整理字符串（默认去掉字符串前后的空格）
trim(' hello ') -- 返回 'hello'
-- 如下，最终插入李四
insert into tb_temp (temp_name) values (trim('a' from 'aaaaaaaaaaaaaaaaa李四aaaaaaaaaaaaaaaaaa'));
-- lpad 用指定的字符实现左填充指定长度
lpad('hello',7,*) -- '**hello'
-- rpad 与上同理，但是是右填充
-- 如果原字符串长度大于所填长度，则会把超出的那部分删掉
-- 字符替换
replace('hello world hello alsritter','hello','你好')
-- 输出为 '你好 world 你好 alsritter'
数学函数
-- round 四舍五入
round(1.55) -- 2
-- 保留两位 1.57
round(1.567,2)
-- ceil 向上取整(返回`>=`该参数的最小整数) & floor 向下取整(返回`<=`该参数的最大整数)
ceil(1.001) -- 2
floor(1.001) -- 1
-- truncate 截断，保留几位小数
truncate(1.699999,1) -- 1.6
-- rand 获取随机数，返回 0-1 之间的小数(无限接近于1，但是取不了1)
insert into tb_temp (temp_name, temp_age) values ('艾丽娅', RAND() * 100);
日期函数
-- 返回当前系统 datetime
now()
-- 返回当前系统 date
curdate()
-- 返回当前系统 time
curtime()
-- 返回某个时间要素：年、月、日、小时、分钟、秒
year()
mouth()
mouthname() -- 月份的英文
day()
minute()
second()
-- 混合使用
year(NOW())
year('1998-1-1')
-- str_to_date 将日期格式的字符转换成指定格式的日期
str_to_date('9-13-1999','%m-%d-%y') -- 1999-09-13
-- %Y 四位的年份
-- %y 两位的年份
-- %m 月（01，02，03....）
-- %c 月（1，2，3....）
-- %d 日
-- %H 小时，24小时制
-- %h 小时，12小时制
-- %i 分钟
-- %s 秒
-- date_format 将日期转换成字符
date_format('20181616','%Y年%m月%d日')
-- datediff 计算相差的天
datediff('2020-1-1','1995-1-1')
工具函数
-- md5('字符串') 返回当前字符串的 md5 加密形式
-- MD5加密
INSERT into tb_temp(name, pwd) values ('小美',md5('123456'));
-- 给表的某个字段全局加密
update tb_temp set pwd = MD5(pwd);
-- 校对方法：将用户传递进来的密码，进行MD5加密，然后比对加密后的值
-- 去重
select distinct 字段名 from 表名
-- 处理 null ，语法：ifnull('需要检验的字段' , '如果为 null 则显示的值')
select ifnull(temp_name, '未命名') from tb_temp;
-- 注意：这里不会修改原数据，只是 null 的字段显示为 未命名
系统函数
-- 查看系统版本号
select version();
-- 查看当前数据库
select database();
-- 查看全部库
show databases;
-- 查看当前用户
select user();
流程控制函数
CASE 结构类似于 Java 中的 switch 语句
case 要判断的字段或表达式
where 常量1 then 结果1或者语句1
where 常量2 then 结果2或者语句2
where 常量3 then 结果3或者语句3
....
else 要显示的语句n
end
select 中使用
select 工资 as 原工资, 部门id,
case 部门id
where 1 then 工资 * 1.1
where 2 then 工资 * 1.2
where 3 then 工资 * 1.3
....
else 工资
end as 新工资
from 表;
/*写法1：类似于java中的if else*/
SELECT id 编号,(CASE sex WHEN 1 THEN '男' ELSE '女' END) 性别,name 姓名 FROM t_user;
/*写法2：类似于java中的if else if*/
SELECT id 编号,(CASE sex WHEN 1 then '男' WHEN 2 then '女' END) 性别,name 姓名 FROM t_user;
自定义函数
-- 创建一个临时用户表
CREATE TABLE IF NOT EXISTS `testUser_tb`
(
`id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户编号',
`username` VARCHAR(20) NOT NULL,
`age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT '年龄',
`tel` CHAR(11) NOT NULL
) ENGINE = INNODB
DEFAULT CHARSET = UTF8;
-- 创建一个函数
DELIMITER $$ -- 写函数前必须要写，类似于标志符
-- 定义一个函数用来创建十万条数据
create function mock_data()
RETURNS INT -- 返回类型
BEGIN
-- 函数体
DECLARE num INT DEFAULT 100000; -- 定义一个变量为十万
DECLARE i INT DEFAULT 0;
while i < num
DO
insert into testUser_tb (username, age, tel)
-- 插入数据
-- RAND()随机生成1-0之间的小数，跟上后面的生成随机的电话号码
-- FLOOR()因为上面的那个操作会产生小数，所以向下取整
values (concat('张三', i),
FLOOR(RAND() * 100),
FLOOR(concat('131', RAND() * ((99999999) - 10000000) + 10000000)));
set i = i + 1;
end while;
RETURN i;
END $$;
-- 执行函数
select mock_data();
触发器
CREATE TRIGGER before_insert_app_users
BEFORE INSERT ON app_users
FOR EACH ROW -- 为每一行
IF new.uuid IS NULL
THEN
SET new.uuid = uuid();
END IF;